Методы прогнозирования сезонных колебаний

Проблемы в прогнозировании объемов продаж всегда остаются актуальными для фирмы, занимающейся разработкой стратегии своей деятельности. Мате­матическая статистика предлагает довольно обширный перечень методов, кото­рые могут быть использованы для прогнозов. Наиболее простым из методов про­гнозирования является метод экстраполяции тренда динамического ряда, исчис­ленного за текущий период. Тренд выражает наблюдаемую тенденцию динамики посредством линейных или нелинейных функций времени, получаемых методом наименьших квадратов (МНК) или иным способом.

Таблица 1.1

Ежемесячный объем продаж туристических путевок в

санатории Крыма, млн.  руб.

Месяц/Год	2000 г	2001 г	2002 г	2003 г	2004 г
Январь	7,925	8,401	8,485	8,848	9,415
Февраль	7,374	7,797	8,382	8,753	9,077
Март	8,94	10,238	10,563	11,155	11,224
Апрель	9,769	10,406	10,937	10,898	11,503
Май	10,126	11,217	10,998	11,917	12,065
Июнь	9,772	11,891	12,587	12,955	12,801
Июль	11,371	11,971	12,557	12,131	13,008
Август	11,896	11,057	11,976	12,752	12,92
Сентябрь	10,511	10,49	10,906	11,016	11,731
Октябрь	9,944	9,701	9,72	10,493	10,965
Ноябрь	8,853	8,794	9,56	9,832	10,26
Декабрь	9,312	9,638	9,745	9,355	10,513
Итого	115,793	121,601	126,416	130,105	135,482

 

Желательно при прогнозах объемов продаж разработать несколько вариантов прогноза с использованием нескольких методов, которые в дальнейшем можно сопоставить между собой, сравнить с фактическими данными и выбрать наиболее адекватную модель. Для выполнения данной работы студенту предлагается провести расчеты по анализу и прогнозированию объемов продаж туристических путевок в санатории Крыма за последние четыре года (таблица 1.1) с использованием электронных таблиц МS  Ехсеl.

Задание: провести сравнительный анализ методов прогнозирования объемов продаж  с ярко выраженными сезонными колебаниями, выбрать наиболее адекватный из них для данного товара и составить прогноз  продаж туристических путевок  на следующий год по месяцам.

В работе должны использоваться три наиболее популярных метода прогноза: (1) на основе индекса сезонности; (2) на основе сезонной компоненты и (3) ряды Фурье с одной или двумя гармониками ряда.

1. Метод сезонных колебаний

Прогнозирование на основе временных рядов предполагает, что происхо­дившие изменения в объемах продаж могут быть использованы для определения этого показателя в последующие периоды времени. Перед составлением прогноза необходимо выявить общую тенденцию развития рассматриваемого явления, что возможно при составлении линии тренда. Линия тренда имеет вид

                            (1.1)

На рис. 1 видно, что ежегодные объемы продаж туристических путевок растут по линейной зависимости. Степень аппроксимации построенной прямой имеет высокое значение (R²=0,9902), что говорит об адекватности построенного тренда (об адекватности можно говорить если R² >0,75). На основе тренда можно определить прогнозные значения объема продаж на следующий год  (см. табл. 1.2).

Таблица 1.2

   Объемы продаж фактические и на основе тренда, млн. руб.

Год	2000 г	2001 г	2002 г	2003 г	2004 г
Значения Х	1	2	3	4	5
Vфактич.	115,793	121,601	126,416	130,105
Vтренд.	116,3151	121,0902	125,8653	130,6404	135,4155

Для выбора наиболее адекватного метода необходимо провести прогнозные расчеты с шагом назад, т.е. за четыре года (2000 г., 2001 г., 2002 г. и 2003 г.), и сравнить их с имеющимися фактическими данными за 2004 год.

Одним из статистических методов прогнозирования является расчет прогнозов на основе сезонных колебаний уровней динамического ряда. При этом под сезонными колебаниями понимаются та­кие изменения уровней ряда, которые вызываются влиянием времени года. Сезонные колебания строго цикличны — они повторяются ежегодно. Методика статистического прогноза по сезонным колебаниям основана на их экстраполяции, т.е. на предположении, что параметры сезонных колебаний со­храняются до прогнозируемого периода. Для измерения сезонных колебаний обычно

Рис. 1.1   Ежегодный объем продаж

применяются индексы сезонности (Js), рассчитываемые по следующей формуле:

(1.2)

где V_ср.мес.— среднемесячный объем продаж, млн. руб., V_ср.год. – среднегодовой объем продаж, млн. руб. прогнозируемые объемы продаж рассчитываются по формуле:

(1.3)

где V_тр – объем продаж на прогнозируемый период, рассчитанный на основе уравнения тренда (табл. 1.2). Расчет осуществляется по формулам (1.2) и (1.3), результат оформляем в таблице 1.3.

Таблица 1.3

Прогноз объемов продаж методом сезонных колебаний

Месяц	Объёмы продаж, млн.руб.				Расчётные графы			Прогноз на 2004 г
	2000 г	2001 г	2002 г	2003 г	Сумма	Vср.мес.	Js, %
	1	2	3	4	5	6	7	8
Январь	7,925	8,401	8,485	8,848	33,659	8,41475	81,77682	9,2282
Февраль	7,374	7,797	8,382	8,753	32,306	8,0765	78,48962	8,8572
Март	8,94	10,238	10,563	11,155	40,896	10,224	99,35961	11,2123
Апрель	9,769	10,406	10,937	10,898	42,01	10,5025	102,0661	11,5177
Май	10,126	11,217	10,998	11,917	44,258	11,0645	107,5278	12,1341
Июнь	9,772	11,891	12,587	12,955	47,205	11,80125	114,6877	12,9420
Июль	11,371	11,971	12,557	12,131	48,03	12,0075	116,6921	13,1682
Август	11,896	11,057	11,976	12,752	47,681	11,92025	115,8442	13,0725
Сентябрь	10,511	10,49	10,906	11,016	42,923	10,73075	104,2843	11,7680
Октябрь	9,944	9,701	9,72	10,493	39,858	9,9645	96,83771	10,9277
Ноябрь	8,853	8,794	9,56	9,832	37,039	9,25975	89,98876	10,1548
Декабрь	9,312	9,638	9,745	9,355	38,05	9,5125	92,44506	10,4320
Итого	115,793	121,601	126,416	130,105	V ср.год.:	10,2899	Vтр.2004:	135,4155

 

Для оценки прогноза на 2004 год методом сезонных колебаний построим диаграмму

Рис. 1.2   Прогноз методом сезонных колебаний

2. Метод сезонной компоненты

 Метод также может быть использован для прогнозирования продаж сезонного характера. В первую очередь для использования данного метода необходимо определить тренд, наилучшим образом аппроксимирующий фактические данные. Циклические явления лучше всего аппроксимируются полиномиальным трендом второго и выше порядка. При выборе наибольшей степени полиномиального тренда необходимо пользоваться практическим правилом, что степень полинома должна отличатся на порядок от количества данных и не превосходить 3 или 4. На рис. 1.3 представлены уравнения линий тренда с различной степенью полиномиальности (используются данные за 2001, 2002, 2003 г.).

Рис. 1. 3. Выбор трендовой модели

 

В таблице 1.4 приведены расчеты для выбора наиболее адекватного тренда (в нашем случае данных 36, следовательно, строим 4 полинома).

Таблица 1.4

Выбор линии тренда

№ месяца	Факт.объём	Полином 1-ой степени,	0,25179357	Отклонение	Квадрат отклонения	Полином 2-ой степени,	0,27892651	Отклонение	Квадрат отклонения	Полином 3-ей степени,	0,27892651	Отклонение	Квадрат отклонения	Полином 4-ой степени,	0,47127487	Отклонени	Квадрат отклонения
			R=				R=				R=				R=
1	2	3		4=2-3	5	6		7=2-6	8	9		10=2-9	11	12		13=2-12	14
1	8,40	9,92		-1,52	2,32	9,59		-1,19	1,41	9,58		-1,18	1,38	8,40		0,00	0,00
2	7,80	9,96		-2,16	4,67	9,68		-1,88	3,54	9,67		-1,87	3,51	9,17		-1,37	1,87
3	10,24	9,99		0,25	0,06	9,77		0,47	0,22	9,76		0,48	0,23	9,75		0,49	0,24
4	10,41	10,02		0,38	0,15	9,85		0,56	0,31	9,85		0,56	0,31	10,18		0,23	0,05
5	11,22	10,06		1,16	1,35	9,93		1,29	1,66	9,93		1,29	1,66	10,48		0,74	0,55
6	11,89	10,09		1,80	3,24	10,01		1,88	3,55	10,01		1,88	3,54	10,66		1,23	1,51
7	11,97	10,12		1,85	3,42	10,08		1,89	3,57	10,08		1,89	3,56	10,75		1,22	1,48
8	11,06	10,16		0,90	0,81	10,15		0,90	0,82	10,16		0,90	0,81	10,78		0,28	0,08
9	10,49	10,19		0,30	0,09	10,22		0,27	0,07	10,22		0,27	0,07	10,74		-0,25	0,06
10	9,70	10,22		-0,52	0,27	10,28		-0,58	0,34	10,29		-0,59	0,34	10,66		-0,96	0,93
11	8,79	10,26		-1,46	2,14	10,34		-1,55	2,40	10,35		-1,55	2,41	10,56		-1,77	3,12
12	9,64	10,29		-0,65	0,42	10,40		-0,76	0,58	10,40		-0,76	0,58	10,44		-0,80	0,65
13	8,49	10,32		-1,84	3,37	10,45		-1,97	3,88	10,45		-1,97	3,88	10,32		-1,83	3,36
14	8,38	10,35		-1,97	3,89	10,50		-2,12	4,50	10,50		-2,12	4,50	10,20		-1,81	3,29
15	10,56	10,39		0,18	0,03	10,55		0,01	0,00	10,55		0,01	0,00	10,08		0,48	0,23
16	10,94	10,42		0,52	0,27	10,59		0,34	0,12	10,59		0,35	0,12	9,99		0,95	0,90
17	11,00	10,45		0,54	0,30	10,63		0,36	0,13	10,63		0,37	0,14	9,91		1,09	1,19
18	12,59	10,49		2,10	4,41	10,67		1,92	3,67	10,66		1,92	3,70	9,85		2,74	7,50
19	12,56	10,52		2,04	4,15	10,70		1,85	3,43	10,70		1,86	3,46	9,81		2,75	7,56
20	11,98	10,55		1,42	2,02	10,73		1,24	1,54	10,72		1,25	1,57	9,79		2,19	4,80
21	10,91	10,59		0,32	0,10	10,76		0,15	0,02	10,75		0,16	0,02	9,78		1,13	1,27
22	9,72	10,62		-0,90	0,81	10,78		-1,06	1,13	10,77		-1,05	1,10	9,78		-0,06	0,00
23	9,56	10,65		-1,09	1,19	10,80		-1,24	1,55	10,79		-1,23	1,51	9,79		-0,23	0,05
24	9,75	10,69		-0,94	0,88	10,82		-1,07	1,15	10,80		-1,06	1,12	9,79		-0,04	0,00
25	8,85	10,72		-1,87	3,50	10,83		-1,98	3,94	10,81		-1,97	3,86	9,77		-0,92	0,85
26	8,75	10,75		-2,00	3,99	10,84		-2,09	4,36	10,82		-2,07	4,28	9,73		-0,97	0,95
27	11,16	10,78		0,37	0,14	10,85		0,31	0,09	10,83		0,33	0,11	9,64		1,51	2,28
28	10,90	10,82		0,08	0,01	10,85		0,05	0,00	10,83		0,07	0,00	9,50		1,39	1,94
29	11,92	10,85		1,07	1,14	10,85		1,07	1,14	10,83		1,09	1,19	9,29		2,63	6,89
30	12,96	10,88		2,07	4,29	10,85		2,11	4,45	10,82		2,13	4,55	8,99		3,97	15,74
31	12,13	10,92		1,21	1,47	10,84		1,29	1,67	10,81		1,32	1,73	8,57		3,56	12,66
32	12,75	10,95		1,80	3,25	10,83		1,93	3,71	10,80		1,95	3,80	8,02		4,73	22,34
33	11,02	10,98		0,03	0,00	10,81		0,20	0,04	10,79		0,23	0,05	7,32		3,70	13,66
34	10,49	11,02		-0,52	0,27	10,79		-0,30	0,09	10,77		-0,28	0,08	6,43		4,06	16,48
35	9,83	11,05		-1,22	1,48	10,77		-0,94	0,89	10,75		-0,92	0,85	5,34		4,49	20,20
36	9,36	11,08		-1,73	2,98	10,75		-1,39	1,94	10,73		-1,38	1,89	4,00		5,35	28,62
				S	1,85			S	1,88			S	1,94			S	5,91

Выбор линии тренда проводится по среднему квадрату ошибок

,

где — количество наблюдений (), — число параметров кривой приближения (для линейной функии , для квадратичной функции  и т. д.). Как видно из таблицы 1.4, самую  наименьшая средний квадрат ошибок отклонений выходит у полинома первой степени. Следовательно, полином первой степени является наиболее адекватным трендом.

По выбранной линии определяются значения тренда, которые сравниваются с фактическими данными, затем вычисляются сезонные компоненты по годам и средняя компонента. Расчеты приведены в табл. 1.5.  Значения средней сезонной компоненты корректируются таким образом, чтобы их сумма была равна нулю.

Таблица 1.5

Расчет средних значений сезонной компоненты

Месяцы	Величина отклонения 2001 г	Величина отклонения 2002 г	Величина отклонения 2003 г	Итого	Среднее за 3 года	Сезонная компонента
1	2	3	4	5	6	7
Январь	-1,52	-1,84	-1,87	-5,23	-1,74	-1,74
Февраль	-2,16	-1,97	-2,00	-6,13	-2,04	-2,04
Март	0,25	0,18	0,37	0,79	0,26	0,26
Апрель	0,38	0,52	0,08	0,98	0,33	0,33
Май	1,16	0,54	1,07	2,77	0,92	0,92
Июнь	1,80	2,10	2,07	5,97	1,99	1,99
Июль	1,85	2,04	1,21	5,10	1,70	1,70
Август	0,90	1,42	1,80	4,13	1,38	1,38
Сентябрь	0,30	0,32	0,03	0,65	0,22	0,22
Октябрь	-0,52	-0,90	-0,52	-1,94	-0,65	-0,65
Ноябрь	-1,46	-1,09	-1,22	-3,77	-1,26	-1,26
Декабрь	-0,65	-0,94	-1,73	-3,32	-1,11	-1,11
Итого					0,00	0,00
Среднемесячное отклонение					0,00

 

На основе модели строится окончательный прогноз объема продаж. Для смягчения влияния прошлых тенденций на достоверность прогнозной модели предлагается сочетать трендовый анализ с экспоненциальным сглаживанием

,      (1.4)

где Vnp_t– прогнозное значение объема продаж, Vф_t-1 – фактическое значение объема продаж в предыдущем году, Vm_t– значение модели, α – значение параметра сглаживания.

При построении прогнозов с помощью метода сезонной компоненты одной из основных проблем является выбор оптимального значения параметра сглаживания  α.  Ясно, что при различных значениях α ре­зультаты прогноза будут различными. Вообще значение α может быть от 0 до 1. В литературе по статистике рекомендуется применять значение α от 0,2 до 0,3. результаты прогноза представлены в табл. 1.6 (считаем, что  α=0,3).

Таблица 1.6

Расчет прогноза продаж

Для оценки прогноза на 2004 год методом сезонной компоненты построим диаграмму

Рис. 1.4.   Прогноз методом сезонной компоненты

1.3 Метод Фурье

Выявить годовую цикличность можно и с помощью аналитических уравнений ряда Фурье. Такой динамический ряд может быть описан уравнением в виде:

                               (1.5)

где a, b, d  –  коэффициенты, вычисляемые по формулам, m – количество гармоник (в данной работе m=1 или m=2):

где  — объем продаж по месяцам за 2003 год. Учитывая, что периодические колебания, в частности сезонные, регулярно повторяются из года в год, можно взять n=12, тогда:

	(1.6)
	(1.7)
	(1.8)

Ряд динамики можно записать в следующем виде  ().

Таблица 1.7

Ряд динамики

V1

V2

V3

V4

V5

V6

V7

V8

V9

V10

V11

V12

0

π/6

π/3

π/2

2π/3

5π/6

π

7π/6

4π/3

3π/2

5π/3

11π/6

Построим две модели сезонной волны по первым двум гармоникам ряда Фурье. Формула для расчета ряда динамики с учетом первой волны записывается так:

 

(1.9)

По данным таблицы 1.8 рассчитаем параметры уравнения:

а = 130,105 / 12 = 10,8421

b₁= -10,837 / 6 = -1,806

d₁ = 2,132 / 6 = 0,35526

Следовательно, с учетом первой гармоники ряд динамики описывается уравнением:

Таблица 1.8

Ряд Фурье с учетом первой гармоники ряда

Месяц	2003 г. ()							Отклонение  (2-8)	Квадрат отклонения
1	2	3	4	5	6	7	8	9	10
Январь	8,848	0,000	1,000	0,000	8,848	0,000	9,036	-0,188	0,035
Февраль	8,753	0,524	0,866	0,500	7,580	4,377	9,456	-0,703	0,494
Март	11,155	1,047	0,500	0,866	5,578	9,661	10,247	0,908	0,825
Апрель	10,898	1,571	0,000	1,000	0,000	10,898	11,197	-0,299	0,090
Май	11,917	2,094	-0,500	0,866	-5,958	10,320	12,053	-0,136	0,018
Июнь	12,955	2,618	-0,866	0,500	-11,219	6,478	12,584	0,371	0,138
Июль	12,131	3,142	-1,000	0,000	-12,131	0,000	12,648	-0,517	0,268
Август	12,752	3,665	-0,866	-0,500	-11,044	-6,376	12,229	0,523	0,274
Сентябрь	11,016	4,189	-0,500	-0,866	-5,508	-9,540	11,437	-0,421	0,178
Октябрь	10,493	4,712	0,000	-1,000	0,000	-10,493	10,487	0,006	0,000
Ноябрь	9,832	5,236	0,500	-0,866	4,916	-8,515	9,631	0,201	0,040
Декабрь	9,355	5,760	0,866	-0,500	8,102	-4,678	9,100	0,255	0,065
Итого	130,105				-10,837	2,132			2,424

 

Рассчитаем по тем же исходным данным  с учетом второй гармоники по формуле:

(1.10)

По данным таблицы 9 определяем параметры b₂ и d₂ :

b₂ = — 0,465 / 6 =  — 0,0774 и d₂ = — 0,332 / 6= — 0,0553.

С учетом второй гармоники получено следующее уравнение (см. табл. 1.9):

Вычисленные суммы квадратов отклонений выровненных значений от ис­ходных данных равны:

— с учетом первой гармоники — 2,424;

— с учетом второй гармоники — 2,396.

Следовательно, расчет с учетом второй гармоники более полно воспроизво­дит сезонную волну.

Таблица 1.9

Ряд Фурье с учетом второй  гармоники ряда

Месяц	2003 г. ()							Отклонение  (2-8)	Квадрат отклонения
1	2	3	4	5	6	7	8	9	10
Январь	8,848	0,000	1,000	0,000	8,848	0,000	8,959	-0,111	0,012
Февраль	8,753	0,524	0,500	0,866	4,377	7,580	9,369	-0,616	0,379
Март	11,155	1,047	-0,500	0,866	-5,578	9,661	10,238	0,917	0,842
Апрель	10,898	1,571	-1,000	0,000	-10,898	0,000	11,275	-0,377	0,142
Май	11,917	2,094	-0,500	-0,866	-5,959	-10,320	12,139	-0,222	0,049
Июнь	12,955	2,618	0,500	-0,866	6,478	-11,219	12,593	0,362	0,131
Июль	12,131	3,142	1,000	0,000	12,131	0,000	12,571	-0,440	0,193
Август	12,752	3,665	0,500	0,866	6,376	11,044	12,142	0,610	0,372
Сентябрь	11,016	4,189	-0,500	0,866	-5,508	9,540	11,428	-0,412	0,170
Октябрь	10,493	4,712	-1,000	0,000	-10,493	0,000	10,564	-0,071	0,005
Ноябрь	9,832	5,236	-0,500	-0,866	-4,916	-8,515	9,718	0,114	0,013
Декабрь	9,355	5,760	0,500	-0,866	4,677	-8,102	9,061	0,294	0,087
Итого	130,105				-0,465	-0,332			2,396

Однако, для прогноза этого недостаточно. Так как тенденция объема продаж имеет направление к росту, следовательно, на будущий период амплитуда сезонной волны должна быть выше. Мы определили линию тренда (1), которая имеет вид:

V(  t ) = 4,7751 t + 111,54.

Коэффициент при t показывает среднегодовой прирост объема продаж, ко­торый и может быть использован для прогноза. В рядах Фурье время  для каждого месяца выражается через π с соответст­вующим коэффициентом, которые, в свою очередь, и выражают амплитуду изме­нения объема продаж на каждый месяц. Если общегодовой прирост «разбросать» по каждому месяцу согласно его амплитуде, то получим прирост объема продаж на каждый месяц. Для этого ,  выраженных через π, приравнивается к среднегодо­вому приросту, а затем через простейшие пропорции определяется прирост за со­ответствующий месяц, т.е.

Таблица 1.10

Корректировка прогноза объема продаж

Месяц					(4+3)	(5+3)
1	2	3	4	5	6	7
Январь	0,000	0,000	9,036	8,959	9,036	8,959
Февраль	0,524	0,072	9,456	9,369	9,528	9,441
Март	1,047	0,145	10,247	10,238	10,391	10,382
Апрель	1,571	0,217	11,197	11,275	11,414	11,492
Май	2,094	0,289	12,053	12,139	12,342	12,429
Июнь	2,618	0,362	12,584	12,593	12,946	12,955
Июль	3,142	0,434	12,648	12,571	13,082	13,005
Август	3,665	0,506	12,229	12,142	12,735	12,648
Сентябрь	4,189	0,579	11,437	11,428	12,016	12,007
Октябрь	4,712	0,651	10,487	10,564	11,138	11,215
Ноябрь	5,236	0,723	9,631	9,718	10,355	10,441
Декабрь	5,760	0,796	9,100	9,061	9,896	9,857
Итого	34,558	4,775	130,105	130,056	134,880	134,832

Для оценки прогноза на 2004 год методом Фурье с одной гармоникой построим диаграмму

 

Рис. 1.5.   Прогноз методом Фурье с одной гармоникой

Для оценки прогноза на 2004 год методом Фурье с двумя гармониками построим диаграмму

Рис. 1.6.   Прогноз методом Фурье с двумя гармониками

1.4 Сводный анализ

  Для сопоставления результатов всех, приведенных в методическом указании расчетов, все прогнозы сводятся в одну  таблицу (см. табл. 1.11).

Таблица 1.11

Сравнительная таблица прогнозов

Месяц	Фактический объём за 2004 г, млн.руб.	Метод сезонных колебаний			Метод сезонной компоненты			Ряд Фурье с 1-ой гармоникой			Ряд Фурье со 2-ой гармоникой
		Прогнозный объём, млн.руб.	Отклонение	Квадрат отклонений	Прогнозный объём, млн.руб.	Отклонение	Квадрат отклонений	Прогнозный объём, млн.руб.	Отклонение	Квадрат отклонений	Прогнозный объём, млн.руб.	Отклонение	Квадрат отклонений
1	2	3	4=3-2	5	6	7=6-2	8	9	10=9-2	11	12	13=12-2	14
Январь	9,42	9,23	-0,19	0,03	9,22	-0,20	0,04	9,04	-0,38	0,14	8,96	-0,46	0,21
Февраль	9,1	8,9	-0,2	0,0	9,0	-0,1	0,0	9,5	0,5	0,2	9,4	0,4	0,1
Март	11,2	11,2	0,0	0,0	11,4	0,1	0,0	10,4	-0,8	0,7	10,4	-0,8	0,7
Апрель	11,5	11,5	0,0	0,0	11,3	-0,2	0,0	11,4	-0,1	0,0	11,5	0,0	0,0
Май	12,1	12,1	0,1	0,0	12,1	0,0	0,0	12,3	0,3	0,1	12,4	0,4	0,1
Июнь	12,8	12,9	0,1	0,0	13,2	0,4	0,1	12,9	0,1	0,0	13,0	0,2	0,0
Июль	13,0	13,2	0,2	0,0	12,7	-0,3	0,1	13,1	0,1	0,0	13,0	0,0	0,0
Август	12,9	13,1	0,2	0,0	12,7	-0,2	0,0	12,7	-0,2	0,0	12,6	-0,3	0,1
Сентябрь	11,7	11,8	0,0	0,0	11,4	-0,3	0,1	12,0	0,3	0,1	12,0	0,3	0,1
Октябрь	11,0	10,9	0,0	0,0	10,7	-0,3	0,1	11,1	0,2	0,0	11,2	0,3	0,1
Ноябрь	10,3	10,2	-0,1	0,0	10,1	-0,2	0,0	10,4	0,1	0,0	10,4	0,2	0,0
Декабрь	10,5	10,4	-0,1	0,0	10,1	-0,4	0,2	9,9	-0,6	0,4	9,9	-0,6	0,4
Итого	135,5	135,4		0,2	133,9		0,7	134,9		1,7	134,9		1,8

 

Как видно из табл. 1.11, в примере наименьшая сумма квадратов отклонений принадлежит методу сезонных колебаний,  а следовательно этот метод является наиболее адекватным методом прогноза для данного вида туристических путевок.  Этот метод и будет положен  в основу прогноза.

1.5 Прогноз продаж на 2005 год

Рассчитываем объемы продаж на основе уравнения линии тренда на 2005 год. В таблице 1.12 приведены фактические данные и трендовые значения

Рис. 1.1   Ежегодный объем продаж

 

Таблица 1.12

Объемы продаж фактические и на основе тренда, млн. руб.

Год	2000 г	2001 г	2002 г	2003 г	2004 г	2005 г
Значения Х	1	2	3	4	5	6
Vфактич.	115,793	121,601	126,416	130,105	135,482
Vтренд.	116,2982	121,0864	125,8746	130,6628	135,451	140,2392

 

Рассчитываем прогноз объемов продаж на 2005 год.

Таблица 1.13

Прогноз объемов продаж методом сезонных колебаний

Месяц	Объёмы продаж, млн.руб.			Расчётные графы			Прогноз на 2005 г
	2002 г	2003 г	2004 г	Сумма	Vср.мес.	Js, %
1	2	3	4	5	6	7	8
Январь	8,485	8,848	9,415	26,748	8,916	81,881	9,569
Февраль	8,382	8,753	9,077	26,212	8,737	80,240	9,377
Март	10,563	11,155	11,224	32,942	10,981	100,842	11,785
Апрель	10,937	10,898	11,503	33,338	11,113	102,054	11,927
Май	10,998	11,917	12,065	34,980	11,660	107,081	12,514
Июнь	12,587	12,955	12,801	38,343	12,781	117,376	13,717
Июль	12,557	12,131	13,008	37,696	12,565	115,395	13,486
Август	11,976	12,752	12,920	37,648	12,549	115,248	13,469
Сентябрь	10,906	11,016	11,731	33,653	11,218	103,019	12,039
Октябрь	9,720	10,493	10,965	31,178	10,393	95,442	11,154
Ноябрь	9,560	9,832	10,260	29,652	9,884	90,771	10,608
Декабрь	9,745	9,355	10,513	29,613	9,871	90,651	10,594
Итого	126,416	130,105	135,482	V ср.год.:	10,889	V тренд.2005:	140,239

Таким образом, по составленному прогнозу объем продаж на 2005 год составит 140,239 млн. руб. с пиком продаж в июне  в размере 13,717 млн. руб.

Проведение расчетов в Excel

Практическая часть  выполняется средствами табличного процессора MS Excel. Для достижения цели работы необходимо произвести расчеты в нескольких таблицах. Все расчеты производятся посредством занесения в нужную ячейку формул, по которым программа сама производит расчеты. Формирование формул начинается со знаки «=», затем указывается либо константа, путем набора цифр на клавиатуре, либо переменная, путем  указания адреса ячейки, где находятся данные (это делается нажатием левой кнопки мыши на необходимой ячейке). Затем обязательно ставится математический знак. Кнопки всех знаков находятся на правой стороне клавиатуры рядом с кнопками цифр:

«/» – деление,

«*» – умножение,

«-»  – минус,

. Del

«+» — плюс,

«             » — кнопка для внесения дробных чисел,

 

“^” – возведение в степень (находится в верхнем ряду кнопок клавиатуры, включается при смене языка c русского на английский).

Примеры формул:  1. =C4*D4/100

2. =(Y16+K16)*0,3

3. =$C$4*1000*0,4/C$3*H8

4. =H6^2

При формировании формул следует помнить о приоритетности действий  возведения в степень над действиями умножения и деления, а их, в свою очередь, над действиями сложения и вычитания. Т.е. если  необходимо умножить или разделить сумму или разницу, то действия сложения (вычитания) следует заключить в скобки (пример 2). Чтобы не вносить одинаковые формулы в одном столбце, но по разным строкам можно сделать следующее:  внести формулу в первую ячейку, закрепить ее нажатием кнопки клавиатуры Enter, затем скопировать на остальные строки (см. операцию копирования ниже). При этом адреса ячеек в формуле меняются, т.к. они являются относительными данными. Если необходимо, чтобы какой-либо из адресов оставался одним и тем же (абсолютным), перед копированием его необходимо закрепить. Для этого нужно установить курсор перед необходимым адресом и нажать кнопку F4 (пример 3).

Копирование производится в следующем порядке:

—              выделение нужной ячейки или диапазона ячеек,

—              копирование этих данных в буфер обмена (кнопка  Копировать на панели инструментов Стандартная),

—              выделение диапазона ячеек, куда необходимо копировать формулу или данные,

—              вставка данных из буфера обмена (кнопка Вставить на панели инструментов Стандартная).

После запуска программы MS Excel  откроется окно рабочей книги, состоящей из 3 листов. Следует увеличить количество листов книги до 6. Для этого нужно  подвести маркер мыши к названию любого из 3 листов и нажать правую кнопку мыши. В последовательно появляющихся окнах  выбирать опции «Добавить»→ «Лист»→ «ОК».

Затем следует переименовать названия листов. Для этого нужно последовательно подвести  маркер мыши к названию каждого из 6 листов и нажать правую кнопку мыши. В появившемся  окне выбирать опцию «Переименовать» а затем ввести новые названия:

¨             для первого листа – «Исходные данные»,

¨             для второго листа –  «Метод сезонных колебаний»,

¨             для третьего листа – «Метод сезонной компоненты»,

¨             для четвертого листа – «Ряды Фурье»,

¨             для пятого листа – «Сводный анализ»,

¨             для шестого листа – «Расчет прогноза».

На листе «Исходные данные» в первой строке следует указать название работы, фамилию студента и номер варианта. Затем оформить таблицу 1.1 (см. выше) и внести исходные данные. В строке «Итого» надо посчитать сумму данных по столбцу. Для этого можно воспользоваться кнопкой «Σ» (сумма) панели «Стандартная» или внести соответствующую функцию через меню «Вставка» → «Функция» → «СУММ».

Переходим на  лист  «Метод сезонных колебаний». На основе полученных данных следует сделать линейную диаграмму, соблюдая следующую последовательность действий:

1. Сделать активной ячейку в том месте, где будет располагаться диаграмма.
2. Последовательно выбрать меню «Вставка» → «Диаграмма».
3. В появившемся окне  «Мастер диаграмм (шаг 1…)» выбрать вкладку «Стандартные», а затем тип: «График», рядом выбрать вид: «График с маркерами, помечающими точки данных» и щелкнуть кнопку «Далее».
4. В появившемся окне  «Мастер диаграмм (шаг 2…)» выбрать вкладку «Диапазон данных» далее щелкнуть кнопку в поле «Диапазон» и указать ячейки содержащие данные по строке «Итого» табл. 1.1 за 2000, 2001, 2002, 2003 годы. Еще следует пометить мышкой, что Ряды — в строках и щелкнуть кнопку «Далее».
5. В появившемся окне  «Мастер диаграмм (шаг 3…)» выбрать вкладку «Заголовки» и дать названия диаграмме и осям (см. рис. 1.1). Далее выбрать вкладку «Подписи данных» и пометить мышкой, что Подписи значений – значение и щелкнуть кнопку «Далее».
6. В появившемся окне  «Мастер диаграмм (шаг 4…)» следует пометить мышкой, что Поместить диаграмму на листе:  имеющемся и щелкнуть кнопку «Готово».

Результатом этих действий будет  график. К нему еще следует добавить линию тренда. Для этого  надо сделать активным окно диаграммы, щелкнув на нем 1 раз мышкой и в меню «Диаграмма» выбрать опцию «Добавить линию тренда». В появившемся окне «Линия тренда»  следует выбрать закладку «Тип» и щелкнуть на «Линейная». Затем выбрать закладку «Параметры» и  пометить мышкой окошечки   показывать уравнение на диаграмме  и  поместить на диаграмму величину достоверной аппроксимации R^2 . Затем щелкнуть кнопку «ОК». Результатом этих действий будет появление на диаграмме линии тренда (линия черного цвета), уравнения линейной зависимости  тренда и величины аппроксимации, на основе которой следует сделать вывод об адекватности построенного тренда.

Далее следует  оформить таблицу 1.2. В строку V_факт следует указать данные из строки «Итого» табл. 1.1. А в строке V_тренд следует произвести расчет значений тренда на основе полученного на диаграмме уравнения линейной зависимости  тренда. Для этого следует внести формулу в ячейку, указав вместо Х адрес соответствующей ячейки из строки «Значения Х» и скопировать формулу в остальные ячейки таблицы. На основе полученных данных написать вывод об адекватности тренда  после таблицы.

Оформляем таблицу 1.3.  Названия месяцев и данные фактического объема продаж за четыре года копируем из таблицы 1.1 (во время процедуры копирования можно переходить с одного листа на другой). Далее  формируем формулу для подсчета суммы объема продаж  по месяцам за 4 года при помощи кнопки «Σ» (сумма) панели «Стандартная» или соответствующей функции через меню «Вставка» → «Функция» → «СУММ».

Значения по столбцу 6 рассчитываются как среднеарифметическое, т.е. значение столбца 5 делится на 4. Вносим формулу и копируем ее на все строки. В строке «Vср. год.» столбца 6рассчитываем среднеарифметическое значение по столбцу (сумму значений столбца делим на 12).

Значения по столбцу 7 рассчитываются согласно формуле 1.2 (см. теоретическую часть).

Значения по столбцу 8 рассчитываются следующим образом, Сначала в строку «Итого» столбца копируется значение Vтренд за 2004 год из табл. 1.2. Затем по строке «Январь» столбца вносится формула (см. формулу 1.3 из раздела теоретическая часть). Далее производим копирование этой формулы по всему столбцу.

Переходим на  лист «Метод сезонной компоненты». Здесь следует сделать диаграмму и рассчитать 3 таблицы. Вначале оформляем таблицу 1.4. Для того, чтобы названия столбцов выглядели так как в примере следует отформатировать  необходимые ячейки следующим образом. Сначала следует выделить все эти ячейки. Затем выбрать меню Формат → Ячейки, выбрать вкладку Выравнивание, установить Ориентацию на 90 градусов, поставить галочку в окошке переносить по словам и щелкнуть кнопку ОК. В столбец 2 копируем значения из табл. 1.1 за 2001, 2002 и 2003 год (можно брать данные и за четыре года). На основе этих данных следует сделать график справа от таблицы (порядок действий см. выше). На полученном графике следует добавить четыре линии тренда. Для этого  надо сделать активным окно диаграммы, щелкнув на нем 1 раз мышкой и в меню «Диаграмма» выбрать опцию «Добавить линию тренда». В появившемся окне «Линия тренда»  следует выбрать вкладку «Тип» и щелкнуть на «Полиномиальная» и в активизировавшемся окошечке «степень» ввести  2 (для полинома первой степени в окне «Линия тренда»  следует выбрать вкладку «Тип» и щелкнуть на «Линейная»). Затем выбрать вкладку «Параметры» и  пометить мышкой окошечки   показывать уравнение на диаграмме  и  поместить на диаграмму величину достоверной аппроксимации R^2 . Затем щелкнуть кнопку «ОК». Результатом этих действий будет появление на диаграмме линии тренда (линия черного цвета), уравнения линейной зависимости  тренда и величины аппроксимации. Для добавления еще 3 линий тренда следует повторить эти действия, последовательно изменяя значения степени до 4. Для того чтобы уравнения линейной зависимости  тренда и величины аппроксимации не закрывали друг друга, следует расположить их в разных частях диаграммы. Для этого  достаточно расположить маркер мыши на надписи, нажать левую кнопку мыши и, не отпуская ее, перетащить надпись в нужное место.

Далее следует рассчитать табл. 1.4. В столбцы 3, 6, 9 и 12 вносим формулы уравнений зависимости тренда соответствующей степени полиномиальности, где вместо х  указываем адрес ячейки с номером месяца. В столбце «Отклонение» рассчитывается разница между фактическим объемом за данный месяц и значения по столбцу «Полином …».  Формулу для столбца «Квадрат отклонения» можно сформировать 2 способами: либо воспользоваться примером 4 составления формул, либо воспользоваться функцией «степень» через меню «Вставка» → «Функция». По столбцам «Квадрат отклонения» следует посчитать сумму значений. Для этого можно воспользоваться кнопкой «Σ» (сумма) панели «Стандартная» или внести соответствующую функцию через меню «Вставка» → «Функция» → «СУММ».

После табл. 1.4 следует сделать вывод о наиболее адекватном тренде, на основе которого и будет проводиться прогноз методом экспоненциального сглаживания.

Далее следует оформить таблицу 1.5. Значения для столбцов «Величина отклонения: 2001-й год», «Величина отклонения: 2002-й год» и «Величина отклонения: 2003-й год» копируются из столбца «Отклонение» табл. 1.4 выбранного полинома. В столбце 6 рассчитывается среднеарифметическое значение для столбцов 2, 3 и 4 , т.е. сумму значений делим на 3. В строке «Среднемесячное значение» этого столбца также рассчитывается среднеарифметическое значение, т. е. значение «Итого» по столбцу делим на 12. Значения столбца 7 рассчитываются как разница между средним  и среднемесячным значением.

Далее оформляем таблицу 1.6. В 2 копируем значения табл. 1.1 за 2003 год. Столбец 4 рассчитывается как сумма значений выбранного полинома и столбца 7 табл. 1.5. В строке «Итого» рассчитывается сумма по столбцам. Значение столбца 5 рассчитывается по формуле 1.4 из теоретической части.

Переходим на  лист «Ряды Фурье». Вначале оформляем таблицу 1.7. Затем ниже оформляем таблицу 1.8. В столбец 2 копируем фактические значения объема  с листа «Исходные данные». В столбец 3 вносим формулу для расчета значения t, как указано в табл. 1.7. Для более точного прогноза лучше брать полное значение числа π, поэтому при формировании формулы следует воспользоваться функцией «ПИ», через меню Вставка – Функция – математические. В столбцах 4 и 5 рассчитывается косинус и синус значения t. При формировании формулы следует воспользоваться функцией «COS» и «SIN» через меню Вставка – Функция – математические. Далее рассчитываем значения столбцов 6 и 7. Затем ниже таблицы рассчитываем значения a, b₁, d₁  по формулам 6, 7, 8, используя значения по строке «Итого» столбцов 6 и 7. Значения столбца 8 рассчитываются по формуле 1.9. Далее рассчитывается 9 и 10 столбцы и оформляется таблица 1.9.

Столбцы 2 и 3 таблицы 1.9 аналогичны этим же столбцам табл. 1.8, поэтому их можно скопировать. При расчетах столбцов 4 и 5 следует помнить, что надо брать двойное значение  числа t. После расчета столбцов 6 и 7 следует рассчитать значения b₂ и d₂ по данным строки «Итого» этих столбцов. Значения столбца 8 рассчитываются по формуле 1.10. После расчета столбцов 9 и 10 ниже таблицы следует сделать вывод о том, расчет с учетом какой гармоники наиболее полно воспроизводит сезонную волну. После этого ниже следует оформить таблицу 1.10. Значения t копируем из таблицы 1.8. Значения столбца 2 рассчитываются с учетом коэффициента при х уравнения линейной зависимости  тренда  диаграммы с листа исходные данные и суммы значений столбца 1 таблицы. Значения столбцов 4 и 5 копируются из столбца 8 таблиц 1.8 и 1.9.

Переходим на  лист «Сводный анализ». Здесь оформляем таблицу 1.11.

В столбец 2 копируем данные из табл. 1.1.

В столбец 3 копируем данные столбца 8 табл. 1.3.

В столбец 6 копируем данные столбца 5 табл. 1.6.

В столбец 9 копируем данные столбца 6 табл. 1.10.

В столбец 12 копируем данные столбца 7 табл. 1.10.

Далее следует рассчитать остальные столбцы и значения строки «Итого». После этого ниже таблицы следует сделать вывод о наиболее адекватном методе прогноза на основе данных о наименьшем значении квадрата отклонений.

Переходим на  лист «Расчет прогноза». Здесь следует рассчитать прогнозные значения объема продаж туристических путевок в Крым тем методом, который был выбран на предыдущем листе.

 

Задания для проведения исследований

(Номер страны – последняя цифра номера зачетки).

В таблице 1.14. приведена численность иностранных туристов, прибывших  в Россию, по кварталам (тыс. чел.).

Задание: провести сравнительный анализ методов прогнозирования численности иностранных туристов, прибывших  в Россию, выбрать наиболее адекватный из них  и составить прогноз численности иностранных туристов на следующий год по кварталам. В методе сезонных колебаний, методе сезонной компоненты необходимо  использовать данные за 2004-2008 г.

 

 

	2004				2005				2006				2007				2008				2009
Страна	1 кв.	2 кв.	3 кв.	4 кв.	1 кв.	2 кв.	3 кв.	4 кв.	1 кв.	2 кв.	3 кв.	4 кв.	1 кв.	2 кв.	3 кв.	4 кв.	1 кв.	2 кв.	3 кв.	4 кв.	1.кв	2.кв	3 кв	4 кв
0. Финляндия					33	15	119	63	23	75	100	44	23	53	99	52	21	54	95	59	20	50	90	56
1. Литва	9	16	18	12	12	22	36	7	3	7	12	8	7	15	22	9	9	18	21	9	7	8	20	10
2. Польша					78	247	339	316	154	203	325	274	141	204	275	273	141	204	271	279	140	200	269	270
3. Китай	19	26	82	42	26	61	128	52	30	15	110	48	24	34	107	47	21	36	107	49	19	27	100	50
4. Германия					29	66	166	26	38	50	146	55	32	59	141	35	34	62	141	39	40	65	120	40
5. Эстония	9	12	15	8	5	14	17	8	6	12	25	11	5	13	19	9	2	15	20	19	7	17	22	15
6. Латвия					5	14	13	6	5	10	18	10	6	11	15	8	4	9	17	9	8	9	14	8
7. США	9	21	52	12	6	28	55	21	10	27	69	20	8	25	59	18	11	24	62	19	10	22	60	20
8. Великобритания					5	15	37	10	8	22	44	17	6	18	40	11	5	17	43	9	10	19	40	8
9. Монголия	9	6	7	12	6	6	5	4	3	3	4	4	5	5	5	7	1	2	2	9	2	4	4	8